﻿using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

namespace ExcelDataImporter
{
    public class OleDbHelper
    {
        public static List<string> ReadSheetNames(string fileName)
        {
            using (OleDbConnection conn = PrepareConnection(fileName,true))
            {
                DataTable dataTable= conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                List<string> result=new List<string>();

                if(dataTable!=null)
                {
                    foreach (DataRow dataRow in dataTable.Rows)
                    {
                        result.Add(dataRow["TABLE_NAME"].ToString());
                    }
                }
                return result;
            }
        }

        public static List<string> ReadSheetColumns(string fileName,string sheetName)
        {
            using (OleDbConnection conn = PrepareConnection(fileName, true))
            {
                DataTable dataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, sheetName, null });

                List<string> result = new List<string>();

                if (dataTable != null)
                {
                    foreach (DataRow dataRow in dataTable.Rows)
                    {
                        result.Add(dataRow["Column_NAME"].ToString());
                    }
                }
                return result;
            }
        }

        public static DataTable ReadSheetContent(string fileName,string sheetName,string filter)
        {
            return ReadSheetContent(fileName, sheetName, null, filter);
        }

        public static DataTable ReadSheetContent(string fileName,string sheetName,int? count,string filter)
        {
            using (OleDbConnection conn = PrepareConnection(fileName, true))
            {
                OleDbCommand oleDbCommand=new OleDbCommand();
                if(!count.HasValue)
                    oleDbCommand.CommandText = string.Format("select * from [{0}]", sheetName);
                else
                {
                    oleDbCommand.CommandText = string.Format("select top {1} * from [{0}]", sheetName,count);
                }
                if (!string.IsNullOrEmpty(filter)) oleDbCommand.CommandText +=" "+ filter;
                oleDbCommand.Connection = conn;

                DataSet dataSet=new DataSet();
                OleDbDataAdapter oleDbDataAdapter=new OleDbDataAdapter(oleDbCommand);
                oleDbDataAdapter.Fill(dataSet);

                if (dataSet.Tables.Count > 0)
                    return dataSet.Tables[0];
            }
            return null;
        }

        private static OleDbConnection PrepareConnection(string fileName,bool open)
        {
            OleDbConnection conn= new OleDbConnection();
            if (fileName.EndsWith(".xls"))
            {
                conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Extended Properties='Excel 8.0;HDR=no;IMEX=0'";
            }
            else
            {
                conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0;";
            }
            if(open)
            {
                conn.Open();
            }
            return conn;
        }
    }
}
